﻿-------------------------------------------------------------------------------------------------------
-- spNewRMA is called from Customer Service to create a new RMA record.
-- The ReturnDetail records created are used only to indicate what the customer says that are returning.  
-- When inventory is received InventoryTransaction records are generated to indicate what was returned.
-------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[spNewRMA]
	@OrderID int 
AS
	declare @RMAId int

	if ( select SUM( T1.Qty - coalesce( T2.Qty, 0 ) ) 
		from tblOrderDetail T1
			join ( select T3.OrderDetailID, SUM(Qty) as Qty
				from tblRMAReturn T2
					join tblReturnDetail T3 on T2.RMAReturnID = T3.RMAReturnID
				where T2.ReceiptDate is not null
				group by T3.OrderDetailID ) as T2 on T1.OrderDetailID = T2.OrderDetailID
		where T1.OrderID = @OrderID ) = 0
		RAISERROR ( 'All product on this order has already been returned', 18, -1 )

	insert into tblRMAReturn( OrderID, IssueDate )
	values ( @OrderID, GETDATE() )

	set @RMAId = SCOPE_IDENTITY()
	insert into tblReturnDetail( RMAReturnID, OrderDetailID, Qty )
	select @RMAId, T1.OrderDetailID, T1.Qty - coalesce( T2.Qty, 0 )
	from tblOrderDetail T1
		left outer join ( select T3.OrderDetailID, SUM(Qty) as Qty
			from tblRMAReturn T2
				join tblReturnDetail T3 on T2.RMAReturnID = T3.RMAReturnID
			where T2.ReceiptDate is not null
			group by T3.OrderDetailID ) as T2 on T1.OrderDetailID = T2.OrderDetailID
	where T1.OrderID = @OrderID 
		and T1.Qty - coalesce( T2.Qty, 0 ) > 0

	select @RMAId